﻿
/*
DATE: JANUARY 2010

WRITTEN BY MICHAEL COLES: SERGEANT SQL, http://sqlblog.com/blogs/michael_coles/archive/2010/01/12/t-sql-tuesday-easy-extended-properties.aspx

YOU MAY ALTER THIS CODE AS YOU WISH. KNOCK YOURSELF OUT BUT...THIS CODE AND 
INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED 
OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY 
AND/OR FITNESS FOR A PARTICULAR PURPOSE.
*/

-- Create ExtProps metadata extended properties management schema
CREATE SCHEMA ExtProps AUTHORIZATION [dbo];
GO

-- Create extended property insert procedure
CREATE PROCEDURE ExtProps.PropInsert
	@Object_Name sysname,
	@Property_Name sysname,
	@Property_Value sql_variant
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @Object_Id integer,
		@Object_Type sysname,
		@Part1 sysname,
		@Part2 sysname,
		@Part3 sysname,
		@Part4 sysname,
		@Database_Name sysname,
		@Schema_Name sysname, 
		@Schema_Object_Name sysname, 
		@Column_Name sysname,
		@ERR_MSG varchar(8000),
		@ERR_STA tinyint,
		@ERR_SEV smallint;

	SELECT @Part1 = PARSENAME(@Object_Name, 1),
		@Part2 = PARSENAME(@Object_Name, 2),
		@Part3 = PARSENAME(@Object_Name, 3),
		@Part4 = PARSENAME(@Object_Name, 4),
		@Property_Name = COALESCE(@Property_Name, N'MS_Description');

	IF @Part4 IS NOT NULL
	BEGIN
		RAISERROR (N'Cannot specify 4-part names in PropInsert.', 18, 127)
		WITH NOWAIT, SETERROR;
		IF XACT_STATE() <> 0
			ROLLBACK TRANSACTION;
		RETURN;
	END;

	IF @Part1 IS NULL AND @Part2 IS NULL AND @Part3 IS NULL AND @Part4 IS NULL
	BEGIN -- @Object_Type is DATABASE
		BEGIN TRY
			EXECUTE sys.sp_addextendedproperty
				@name = @Property_Name,
				@value= @Property_Value;
		END TRY
		BEGIN CATCH
			SET @ERR_SEV = ERROR_SEVERITY();
			SET @ERR_STA = ERROR_STATE();
			SET @ERR_MSG = ERROR_MESSAGE() + ' Error occurred while adding property to the current database.';
			RAISERROR(@ERR_MSG, @ERR_SEV, @ERR_STA);
			IF XACT_STATE() <> 0
				ROLLBACK TRANSACTION;
			RETURN;
		END CATCH
	END
	ELSE IF @Part2 IS NULL AND @Part3 IS NULL AND @Part4 IS NULL
	BEGIN
		SET @Object_Type = N'SCHEMA';
		SELECT @Schema_Name = @Part1;
		BEGIN TRY
			EXECUTE sys.sp_addextendedproperty 
				@name = @Property_Name, 
				@value = @Property_Value,
				@level0type = @Object_Type, 
				@level0name = @Schema_Name;
		END TRY
		BEGIN CATCH
			SET @ERR_SEV = ERROR_SEVERITY();
			SET @ERR_STA = ERROR_STATE();
			SET @ERR_MSG = ERROR_MESSAGE() + ' Error occurred while adding property to the [' + @Schema_Name + '] schema.';
			RAISERROR(@ERR_MSG, @ERR_SEV, @ERR_STA);
			IF XACT_STATE() <> 0
				ROLLBACK TRANSACTION;
			RETURN;
		END CATCH
	END
	ELSE IF @Part3 IS NULL AND @Part4 IS NULL
	BEGIN
		SET @Object_Id = OBJECT_ID(@Object_Name);
		SELECT @Object_Type =
			CASE WHEN type IN (N'U', N'IT', N'S') THEN N'TABLE'
				WHEN type = N'V' THEN N'VIEW'
				WHEN type IN (N'P', N'X', N'PC') THEN N'PROCEDURE'
				WHEN type = N'AF' THEN N'AGGREGATE'
				WHEN type IN (N'FT', N'FN', N'IF', N'TF') THEN N'FUNCTION'
				WHEN type = N'SN' THEN N'SYNONYM'
				WHEN type = N'SQ' THEN N'QUEUE'
				END,
			@Schema_Object_Name = @Part1,
			@Schema_Name = @Part2
		FROM sys.objects
		WHERE object_id = @Object_Id;
		BEGIN TRY
			EXECUTE sys.sp_addextendedproperty 
				@name = @Property_Name,
				@value = @Property_Value,
				@level0type = N'SCHEMA', 
				@level0name = @Schema_Name,
				@level1type = @Object_Type, 
				@level1name = @Schema_Object_Name; 
		END TRY
		BEGIN CATCH
			SET @ERR_SEV = ERROR_SEVERITY();
			SET @ERR_STA = ERROR_STATE();
			SET @ERR_MSG = ERROR_MESSAGE() + ' Error occurred while adding property to the [' + @Schema_Name + '].[' + @Schema_Object_Name + '] ' + COALESCE(@Object_Type, '') + '.';
			RAISERROR(@ERR_MSG, @ERR_SEV, @ERR_STA);
			IF XACT_STATE() <> 0
				ROLLBACK TRANSACTION;
			RETURN;
		END CATCH
	END
	ELSE IF @Part4 IS NULL 
	BEGIN
		SELECT @Column_Name = @Part1,
			@Schema_Object_Name = @Part2,
			@Schema_Name = @Part3;

		SET @Object_Id = OBJECT_ID(N'[' + @Schema_Name + '].[' + @Schema_Object_Name + N']');
		SELECT @Object_Type =
			CASE WHEN type IN (N'U', N'IT', N'S') THEN N'TABLE'
				WHEN type = N'V' THEN N'VIEW'
				WHEN type IN (N'P', N'X', N'PC') THEN N'PROCEDURE'
				WHEN type = N'AF' THEN N'AGGREGATE'
				WHEN type IN (N'FT', N'FN', N'IF', N'TF') THEN N'FUNCTION'
				WHEN type = N'SN' THEN N'SYNONYM'
				WHEN type = N'SQ' THEN N'QUEUE'
				END
		FROM sys.objects
		WHERE object_id = @Object_Id;

		BEGIN TRY
			EXECUTE sys.sp_addextendedproperty 
				@name = @Property_Name,
				@value = @Property_Value,
				@level0type = N'SCHEMA', 
				@level0name = @Schema_Name,
				@level1type = @Object_Type, 
				@level1name = @Schema_Object_Name, 
				@level2type = N'COLUMN',
				@level2name = @Column_Name;
		END TRY
		BEGIN CATCH
			SET @ERR_SEV = ERROR_SEVERITY();
			SET @ERR_STA = ERROR_STATE();
			SET @ERR_MSG = ERROR_MESSAGE() + ' Error occurred while adding property to the [' + @Schema_Name + '].[' + @Schema_Object_Name + '].[' + @Column_Name + '] column.';
			RAISERROR(@ERR_MSG, @ERR_SEV, @ERR_STA);
			IF XACT_STATE() <> 0
				ROLLBACK TRANSACTION;
			RETURN;
		END CATCH
	END
END
GO

-- Create extended property delete procedure
CREATE PROCEDURE ExtProps.PropDelete
	@Object_Name sysname,
	@Property_Name sysname
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @Object_Id integer,
		@Object_Type sysname,
		@Part1 sysname,
		@Part2 sysname,
		@Part3 sysname,
		@Part4 sysname,
		@Database_Name sysname,
		@Schema_Name sysname, 
		@Schema_Object_Name sysname, 
		@Column_Name sysname,
		@ERR_MSG varchar(8000),
		@ERR_STA tinyint,
		@ERR_SEV smallint;

	SELECT @Part1 = PARSENAME(@Object_Name, 1),
		@Part2 = PARSENAME(@Object_Name, 2),
		@Part3 = PARSENAME(@Object_Name, 3),
		@Part4 = PARSENAME(@Object_Name, 4),
		@Property_Name = COALESCE(@Property_Name, N'MS_Description');

	IF @Part4 IS NOT NULL
	BEGIN
		RAISERROR (N'Cannot specify 4-part names in PropDelete.', 18, 127)
		WITH NOWAIT, SETERROR;
		IF XACT_STATE() <> 0
			ROLLBACK TRANSACTION;
		RETURN;
	END;

	IF @Part1 IS NULL AND @Part2 IS NULL AND @Part3 IS NULL AND @Part4 IS NULL
	BEGIN -- @Object_Type is DATABASE
		BEGIN TRY
			EXECUTE sys.sp_dropextendedproperty
				@name = @Property_Name;
		END TRY
		BEGIN CATCH
			SET @ERR_SEV = ERROR_SEVERITY();
			SET @ERR_STA = ERROR_STATE();
			SET @ERR_MSG = ERROR_MESSAGE() + ' Error occurred while dropping property from the current database.';
			RAISERROR(@ERR_MSG, @ERR_SEV, @ERR_STA);
			IF XACT_STATE() <> 0
				ROLLBACK TRANSACTION;
			RETURN;
		END CATCH
	END
	ELSE IF @Part2 IS NULL AND @Part3 IS NULL AND @Part4 IS NULL
	BEGIN
		SET @Object_Type = N'SCHEMA';
		SELECT @Schema_Name = @Part1;
		BEGIN TRY
			EXECUTE sys.sp_dropextendedproperty 
				@name = @Property_Name, 
				@level0type = @Object_Type, 
				@level0name = @Schema_Name;
		END TRY
		BEGIN CATCH
			SET @ERR_SEV = ERROR_SEVERITY();
			SET @ERR_STA = ERROR_STATE();
			SET @ERR_MSG = ERROR_MESSAGE() + ' Error occurred while dropping property from the [' + @Schema_Name + '] schema.';
			RAISERROR(@ERR_MSG, @ERR_SEV, @ERR_STA);
			IF XACT_STATE() <> 0
				ROLLBACK TRANSACTION;
			RETURN;
		END CATCH
	END
	ELSE IF @Part3 IS NULL AND @Part4 IS NULL
	BEGIN
		SET @Object_Id = OBJECT_ID(@Object_Name);
		SELECT @Object_Type =
			CASE WHEN type IN (N'U', N'IT', N'S') THEN N'TABLE'
				WHEN type = N'V' THEN N'VIEW'
				WHEN type IN (N'P', N'X', N'PC') THEN N'PROCEDURE'
				WHEN type = N'AF' THEN N'AGGREGATE'
				WHEN type IN (N'FT', N'FN', N'IF', N'TF') THEN N'FUNCTION'
				WHEN type = N'SN' THEN N'SYNONYM'
				WHEN type = N'SQ' THEN N'QUEUE'
				END,
			@Schema_Object_Name = @Part1,
			@Schema_Name = @Part2
		FROM sys.objects
		WHERE object_id = @Object_Id;
		BEGIN TRY
			EXECUTE sys.sp_dropextendedproperty 
				@name = @Property_Name,
				@level0type = N'SCHEMA', 
				@level0name = @Schema_Name,
				@level1type = @Object_Type, 
				@level1name = @Schema_Object_Name; 
		END TRY
		BEGIN CATCH
			SET @ERR_SEV = ERROR_SEVERITY();
			SET @ERR_STA = ERROR_STATE();
			SET @ERR_MSG = ERROR_MESSAGE() + ' Error occurred while dropping property from the [' + @Schema_Name + '].[' + @Schema_Object_Name + '] ' + COALESCE(@Object_Type, '') + '.';
			RAISERROR(@ERR_MSG, @ERR_SEV, @ERR_STA);
			IF XACT_STATE() <> 0
				ROLLBACK TRANSACTION;
			RETURN;
		END CATCH
	END
	ELSE IF @Part4 IS NULL 
	BEGIN
		SELECT @Column_Name = @Part1,
			@Schema_Object_Name = @Part2,
			@Schema_Name = @Part3;

		SET @Object_Id = OBJECT_ID(N'[' + @Schema_Name + '].[' + @Schema_Object_Name + N']');
		SELECT @Object_Type =
			CASE WHEN type IN (N'U', N'IT', N'S') THEN N'TABLE'
				WHEN type = N'V' THEN N'VIEW'
				WHEN type IN (N'P', N'X', N'PC') THEN N'PROCEDURE'
				WHEN type = N'AF' THEN N'AGGREGATE'
				WHEN type IN (N'FT', N'FN', N'IF', N'TF') THEN N'FUNCTION'
				WHEN type = N'SN' THEN N'SYNONYM'
				WHEN type = N'SQ' THEN N'QUEUE'
				END
		FROM sys.objects
		WHERE object_id = @Object_Id;

		BEGIN TRY
			EXECUTE sys.sp_dropextendedproperty 
				@name = @Property_Name,
				@level0type = N'SCHEMA', 
				@level0name = @Schema_Name,
				@level1type = @Object_Type, 
				@level1name = @Schema_Object_Name, 
				@level2type = N'COLUMN',
				@level2name = @Column_Name;
		END TRY
		BEGIN CATCH
			SET @ERR_SEV = ERROR_SEVERITY();
			SET @ERR_STA = ERROR_STATE();
			SET @ERR_MSG = ERROR_MESSAGE() + ' Error occurred while dropping property from the [' + @Schema_Name + '].[' + @Schema_Object_Name + '].[' + @Column_Name + '] column.';
			RAISERROR(@ERR_MSG, @ERR_SEV, @ERR_STA);
			IF XACT_STATE() <> 0
				ROLLBACK TRANSACTION;
			RETURN;
		END CATCH
	END
END
GO

-- Create extended property update procedure
CREATE PROCEDURE ExtProps.PropUpdate
	@Object_Name sysname,
	@Property_Name sysname,
	@Property_Value sql_variant
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @Object_Id integer,
		@Object_Type sysname,
		@Part1 sysname,
		@Part2 sysname,
		@Part3 sysname,
		@Part4 sysname,
		@Database_Name sysname,
		@Schema_Name sysname, 
		@Schema_Object_Name sysname, 
		@Column_Name sysname,
		@ERR_MSG varchar(8000),
		@ERR_STA tinyint,
		@ERR_SEV smallint;

	SELECT @Part1 = PARSENAME(@Object_Name, 1),
		@Part2 = PARSENAME(@Object_Name, 2),
		@Part3 = PARSENAME(@Object_Name, 3),
		@Part4 = PARSENAME(@Object_Name, 4),
		@Property_Name = COALESCE(@Property_Name, N'MS_Description');

	IF @Part4 IS NOT NULL
	BEGIN
		RAISERROR (N'Cannot specify 4-part names in PropUpdate.', 18, 127)
		WITH NOWAIT, SETERROR;
		IF XACT_STATE() <> 0
			ROLLBACK TRANSACTION;
		RETURN;
	END;

	IF @Part1 IS NULL AND @Part2 IS NULL AND @Part3 IS NULL AND @Part4 IS NULL
	BEGIN -- @Object_Type is DATABASE
		BEGIN TRY
			EXECUTE sys.sp_updateextendedproperty
				@name = @Property_Name,
				@value= @Property_Value;
		END TRY
		BEGIN CATCH
			SET @ERR_SEV = ERROR_SEVERITY();
			SET @ERR_STA = ERROR_STATE();
			SET @ERR_MSG = ERROR_MESSAGE() + ' Error occurred while updating property on the current database.';
			RAISERROR(@ERR_MSG, @ERR_SEV, @ERR_STA);
			IF XACT_STATE() <> 0
				ROLLBACK TRANSACTION;
			RETURN;
		END CATCH
	END
	ELSE IF @Part2 IS NULL AND @Part3 IS NULL AND @Part4 IS NULL
	BEGIN
		SET @Object_Type = N'SCHEMA';
		SELECT @Schema_Name = @Part1;
		BEGIN TRY
			EXECUTE sys.sp_updateextendedproperty 
				@name = @Property_Name, 
				@value = @Property_Value,
				@level0type = @Object_Type, 
				@level0name = @Schema_Name;
		END TRY
		BEGIN CATCH
			SET @ERR_SEV = ERROR_SEVERITY();
			SET @ERR_STA = ERROR_STATE();
			SET @ERR_MSG = ERROR_MESSAGE() + ' Error occurred while updating property on the [' + @Schema_Name + '] schema.';
			RAISERROR(@ERR_MSG, @ERR_SEV, @ERR_STA);
			IF XACT_STATE() <> 0
				ROLLBACK TRANSACTION;
			RETURN;
		END CATCH
	END
	ELSE IF @Part3 IS NULL AND @Part4 IS NULL
	BEGIN
		SET @Object_Id = OBJECT_ID(@Object_Name);
		SELECT @Object_Type =
			CASE WHEN type IN (N'U', N'IT', N'S') THEN N'TABLE'
				WHEN type = N'V' THEN N'VIEW'
				WHEN type IN (N'P', N'X', N'PC') THEN N'PROCEDURE'
				WHEN type = N'AF' THEN N'AGGREGATE'
				WHEN type IN (N'FT', N'FN', N'IF', N'TF') THEN N'FUNCTION'
				WHEN type = N'SN' THEN N'SYNONYM'
				WHEN type = N'SQ' THEN N'QUEUE'
				END,
			@Schema_Object_Name = @Part1,
			@Schema_Name = @Part2
		FROM sys.objects
		WHERE object_id = @Object_Id;
		BEGIN TRY
			EXECUTE sys.sp_updateextendedproperty 
				@name = @Property_Name,
				@value = @Property_Value,
				@level0type = N'SCHEMA', 
				@level0name = @Schema_Name,
				@level1type = @Object_Type, 
				@level1name = @Schema_Object_Name; 
		END TRY
		BEGIN CATCH
			SET @ERR_SEV = ERROR_SEVERITY();
			SET @ERR_STA = ERROR_STATE();
			SET @ERR_MSG = ERROR_MESSAGE() + ' Error occurred while updating property on the [' + @Schema_Name + '].[' + @Schema_Object_Name + '] ' + COALESCE(@Object_Type, '') + '.';
			RAISERROR(@ERR_MSG, @ERR_SEV, @ERR_STA);
			IF XACT_STATE() <> 0
				ROLLBACK TRANSACTION;
			RETURN;
		END CATCH
	END
	ELSE IF @Part4 IS NULL 
	BEGIN
		SELECT @Column_Name = @Part1,
			@Schema_Object_Name = @Part2,
			@Schema_Name = @Part3;

		BEGIN TRY
			SET @Object_Id = OBJECT_ID(N'[' + @Schema_Name + '].[' + @Schema_Object_Name + N']');
			SELECT @Object_Type =
				CASE WHEN type IN (N'U', N'IT', N'S') THEN N'TABLE'
					WHEN type = N'V' THEN N'VIEW'
					WHEN type IN (N'P', N'X', N'PC') THEN N'PROCEDURE'
					WHEN type = N'AF' THEN N'AGGREGATE'
					WHEN type IN (N'FT', N'FN', N'IF', N'TF') THEN N'FUNCTION'
					WHEN type = N'SN' THEN N'SYNONYM'
					WHEN type = N'SQ' THEN N'QUEUE'
					END
			FROM sys.objects
			WHERE object_id = @Object_Id;

			EXECUTE sys.sp_updateextendedproperty 
				@name = @Property_Name,
				@value = @Property_Value,
				@level0type = N'SCHEMA', 
				@level0name = @Schema_Name,
				@level1type = @Object_Type, 
				@level1name = @Schema_Object_Name, 
				@level2type = N'COLUMN',
				@level2name = @Column_Name;
		END TRY
		BEGIN CATCH
			SET @ERR_SEV = ERROR_SEVERITY();
			SET @ERR_STA = ERROR_STATE();
			SET @ERR_MSG = ERROR_MESSAGE() + ' Error occurred while updating property on the [' + @Schema_Name + '].[' + @Schema_Object_Name + '].[' + @Column_Name + '] column.';
			RAISERROR(@ERR_MSG, @ERR_SEV, @ERR_STA);
			IF XACT_STATE() <> 0
				ROLLBACK TRANSACTION;
			RETURN;
		END CATCH
	END
END
GO

-- Create extended properties view
CREATE VIEW ExtProps.Properties
AS
SELECT class,
	class_desc,
	CASE class 
		WHEN 1 THEN 
			(
				SELECT SCHEMA_NAME(o.schema_id)
				FROM sys.objects o
				WHERE o.object_id = major_id
			) 
		END AS object_schema,
	CASE class 
		WHEN 0 THEN DB_NAME()
		WHEN 1 THEN OBJECT_NAME(major_id)
		WHEN 3 THEN SCHEMA_NAME(major_id)
		END AS object_name,
	CASE class
		WHEN 1 THEN
			(
				SELECT c.name
				FROM sys.columns c
				WHERE c.object_id = major_id
				AND c.column_id = minor_id
			)
		END AS column_name,
	major_id,
	minor_id,
	name,
	value
FROM sys.extended_properties;
GO

-- Create extended properties view instead of insert trigger
CREATE TRIGGER ExtProps.Properties_Insert
ON ExtProps.Properties
INSTEAD OF INSERT
AS
BEGIN
	SET NOCOUNT ON;
	SET XACT_ABORT ON;
	DECLARE @Object_Schema sysname,
		@Object_Name sysname,
		@Column_Name sysname,		
		@Property_Name sysname,
		@Property_Value sql_variant,
		@Full_Name sysname;

	DECLARE Inserted_Rows CURSOR
	FORWARD_ONLY READ_ONLY
	FOR SELECT object_schema,
		object_name,
		column_name,
		name,
		value
	FROM inserted;

	OPEN Inserted_Rows;

	FETCH NEXT FROM Inserted_Rows
	INTO @Object_Schema,
		@Object_Name,
		@Column_Name,
		@Property_Name,
		@Property_Value;

	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF @Object_Schema IS NULL AND @Object_Name IS NULL AND @Column_Name IS NULL
			EXECUTE ExtProps.PropInsert NULL, @Property_Name, @Property_Value;	-- Database
		ELSE IF @Object_Schema IS NULL AND @Column_Name IS NULL
			EXECUTE ExtProps.PropInsert @Object_Name, @Property_Name, @Property_Value;	-- Schema
		ELSE IF @Column_Name IS NULL
		BEGIN
			SET @Full_Name = COALESCE(N'[' + @Object_Schema + N']', '') + COALESCE(N'.[' + @Object_Name + ']', '');
			EXECUTE ExtProps.PropInsert @Full_Name, @Property_Name, @Property_Value;	-- Table/Proc/Function
		END ELSE
		BEGIN
			SET @Full_Name = COALESCE(N'[' + @Object_Schema + N']', '') + COALESCE(N'.[' + @Object_Name + ']', '') + 
				COALESCE(N'.[' + @Column_Name + N']', '');
			EXECUTE ExtProps.PropInsert @Full_Name, @Property_Name, @Property_Value; -- Column
		END;
		
		FETCH NEXT FROM Inserted_Rows
		INTO @Object_Schema,
			@Object_Name,
			@Column_Name,
			@Property_Name,
			@Property_Value;
	END;
	CLOSE Inserted_Rows;
	DEALLOCATE Inserted_Rows;

END;
GO

-- Create extended proeprties view instead of delete trigger
CREATE TRIGGER ExtProps.Properties_Delete
ON ExtProps.Properties
INSTEAD OF DELETE
AS
BEGIN
	SET NOCOUNT ON;
	SET XACT_ABORT ON;
	DECLARE @Class int,
		@Object_Schema sysname,
		@Object_Name sysname,
		@Column_Name sysname,		
		@Property_Name sysname,
		@Property_Value sql_variant,
		@Full_Name sysname;

	DECLARE Deleted_Rows CURSOR
	FORWARD_ONLY READ_ONLY
	FOR SELECT class,
		object_schema,
		object_name,
		column_name,
		name,
		value
	FROM deleted;

	OPEN Deleted_Rows;

	FETCH NEXT FROM Deleted_Rows
	INTO @Class,
		@Object_Schema,
		@Object_Name,
		@Column_Name,
		@Property_Name,
		@Property_Value;

	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF @Class = 0
			EXECUTE ExtProps.PropDelete NULL, @Property_Name;	-- Database
		ELSE IF @Class = 3
			EXECUTE ExtProps.PropDelete @Object_Name, @Property_Name;	-- Schema
		ELSE IF @Class = 1 AND @Column_Name IS NULL
		BEGIN
			SET @Full_Name = COALESCE(N'[' + @Object_Schema + N']', '') + COALESCE(N'.[' + @Object_Name + ']', '');
			EXECUTE ExtProps.PropDelete @Full_Name, @Property_Name;	-- Table/Proc/Function
		END ELSE
		BEGIN
			SET @Full_Name = COALESCE(N'[' + @Object_Schema + N']', '') + COALESCE(N'.[' + @Object_Name + ']', '') + 
				COALESCE(N'.[' + @Column_Name + N']', '');
			EXECUTE ExtProps.PropDelete @Full_Name, @Property_Name; -- Column
		END;
		
		FETCH NEXT FROM Deleted_Rows
		INTO @Class,
			@Object_Schema,
			@Object_Name,
			@Column_Name,
			@Property_Name,
			@Property_Value;
	END;
	CLOSE Deleted_Rows;
	DEALLOCATE Deleted_Rows;

END;
GO

-- Create extended properties view instead of update trigger
CREATE TRIGGER ExtProps.Properties_Update
ON ExtProps.Properties
INSTEAD OF UPDATE
AS
BEGIN
	SET NOCOUNT ON;
	SET XACT_ABORT ON;
	DECLARE @Class int,
		@Object_Schema sysname,
		@Object_Name sysname,
		@Column_Name sysname,		
		@Property_Name sysname,
		@Property_Value sql_variant,
		@Full_Name sysname;

	DECLARE Updated_Rows CURSOR
	FORWARD_ONLY READ_ONLY
	FOR SELECT class,
		object_schema,
		object_name,
		column_name,
		name,
		value
	FROM inserted;

	OPEN Updated_Rows;

	FETCH NEXT FROM Updated_Rows
	INTO @Class,
		@Object_Schema,
		@Object_Name,
		@Column_Name,
		@Property_Name,
		@Property_Value;

	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF @Class = 0
			EXECUTE ExtProps.PropUpdate NULL, @Property_Name, @Property_Value;	-- Database
		ELSE IF @Class = 3
			EXECUTE ExtProps.PropUpdate @Object_Name, @Property_Name, @Property_Value;	-- Schema
		ELSE IF @Class = 1 AND @Column_Name IS NULL
		BEGIN
			SET @Full_Name = COALESCE(N'[' + @Object_Schema + N']', '') + COALESCE(N'.[' + @Object_Name + ']', '');
			EXECUTE ExtProps.PropUpdate @Full_Name, @Property_Name, @Property_Value;	-- Table/Proc/Function
		END ELSE
		BEGIN
			SET @Full_Name = COALESCE(N'[' + @Object_Schema + N']', '') + COALESCE(N'.[' + @Object_Name + ']', '') + 
				COALESCE(N'.[' + @Column_Name + N']', '');
			EXECUTE ExtProps.PropUpdate @Full_Name, @Property_Name, @Property_Value; -- Column
		END;
		
		FETCH NEXT FROM Updated_Rows
		INTO @Class,
			@Object_Schema,
			@Object_Name,
			@Column_Name,
			@Property_Name,
			@Property_Value;
	END;
	CLOSE Updated_Rows;
	DEALLOCATE Updated_Rows;

END;
GO
